import { StringUtil } from "../../utils/StringUtil";
import { Downloader } from "./model/Downloader";

export class SqlLib {

    constructor(dbFileName, key, iv) {
        this.dbFileName = dbFileName;
        this.sqlite = require('sqlite-sync');
        this.key = key;
        this.sqlite.iv = iv;
        //数组增加contains 方法
        Array.prototype.contains = function (obj) {
            let i = this.length;
            while (i--) {
                if (this[i] === obj) {
                    return true;
                }
            }
            return false;
        }
    }

    static getInstance (dbFileName) {
        if (!this.instance) {
            let iv = Buffer.from(Array.prototype.map.call(Buffer.alloc(16), () => { return Math.floor(Math.random() * 256) }));
            let key = Buffer.concat([Buffer.from("test@1234")], Buffer.alloc(32).length);
            // let key = "Novoedu8996!";
            this.instance = new SqlLib(dbFileName, key, iv);
        }
        return this.instance;
    }

    runSql (sql) {
        // this.sqlite.connect(this.dbFileName, this.key, 'aes-256-ctr');
        this.sqlite.connect(this.dbFileName);
        let result = this.sqlite.run(sql);
        // console.log('result',result);
        this.sqlite.close();
        if (result.error) {
            return "sql error";
        }
        return result;
    }

    //不需要创建连接，由父方法统一创建（不可用）
    runSqlWithoutConnect (sql) {
        let result = this.sqlite.run(sql);
        if (result.error) {
            console.log(result.error);
            return "sql error";
        }
        return result;
    }

    decryptDB (oldFileName, newFileName) {
        this.sqlite.decrypt(oldFileName, newFileName, this.key, "aes-256-ctr");
    }

    encryptDB (oldFileName, newFileName) {
        this.sqlite.encrypt(oldFileName, newFileName, this.key, "aes-256-ctr");
    }

    /**
     * 统一的单个实体插入方法
     * @param obj 任意的数据库实体类
     * @returns {1.param error, 2. sql error, 3.影响的行数}
     */
    insertObject (obj, isConnect = false, isOutSql = false) {
        if (!obj.symbolName || !obj.tableName) {
            return "param error"
        } else {
            try {
                let valueMapper = obj.dbMapper();
                if (!valueMapper) {
                    return "param error"
                }
                let insertSql = Reflect.ownKeys(valueMapper);
                let insertSqlStr = "";
                let valueSqlStr = "";
                insertSql.forEach((value, index) => {
                    let nowKeyValue = typeof obj[value] != undefined ? obj[value] : ''
                    if (index === 0) {
                        insertSqlStr += ("(" + valueMapper[value] + ", ");
                        valueSqlStr += ("('" + nowKeyValue + "', ");
                    } else if (index === insertSql.length - 1) {
                        insertSqlStr += (valueMapper[value] + ")");
                        valueSqlStr += ("'" + nowKeyValue + "' )");
                    } else {
                        insertSqlStr += (valueMapper[value] + ", ");
                        valueSqlStr += ("'" + nowKeyValue + "', ");
                    }
                });
                let sql = StringUtil.format("insert into " + obj.tableName + " {0} values {1}", insertSqlStr, valueSqlStr);
                // console.log(sql);
                if (isOutSql) {
                    console.log(sql);
                }
                let result = isConnect ? this.runSqlWithoutConnect(sql) : this.runSql(sql);
                return result === "sql error" ? result : 1
            } catch (e) {
                return "param error"
            }
        }
    }
    /**
     * 统一的更新单个实体的方法
     * @param obj 任意的数据库实体类
     * @returns {1.param error, 2. sql error, 3.影响的行数}
     */
    updateObjectById (obj, isConnect = false, isOutSql = false) {
        if (!obj.symbolName || !obj.tableName || !obj.id) {
            return "param error"
        } else {
            let valueMapper = obj.dbMapper();
            if (!valueMapper) {
                return "param error";
            }
            let updateKeys = Reflect.ownKeys(valueMapper);
            //check obj 的对象key是否正确
            let objKeys = Reflect.ownKeys(obj);
            let checkResult = true;

            let setArr = [];
            //循环遍历传入对象的key的数组
            for (let i = 0;i < objKeys.length;i++) {
                if (objKeys[i] === "symbolName" || objKeys[i] === "tableName") {
                    continue;
                }
                if (!updateKeys.contains(objKeys[i])) {
                    checkResult = false;
                    break;
                }
                if (objKeys[i] !== "id") {
                    if (typeof obj[objKeys[i]] != undefined && obj[objKeys[i]] !== -1 && obj[objKeys[i]] !== '') {
                        setArr.push(StringUtil.format(" {0} = '{1}' ", valueMapper[objKeys[i]], obj[objKeys[i]]));
                    }
                }
            }
            if (!checkResult) {
                return "param error";
            }

            let sql = StringUtil.format("update {0} set", obj.tableName);
            setArr.forEach((setValue, index) => {
                if (index !== (setArr.length - 1)) {
                    sql = sql + setValue + ",";
                } else {
                    sql = StringUtil.format("{0} {1} where id = '{2}'", sql, setValue, obj.id);
                }
            });
            if (isOutSql) {
                console.log(sql);
            }
            let result = isConnect ? this.runSqlWithoutConnect(sql) : this.runSql(sql);
            return result === "sql error" ? result : 1
        }
    }

    /**
     * 根据id插入或者更新某个对象
     * @param obj
     * @returns {*}
     */
    updateOrInsertObjectById (obj, isConnect = false, isOutSql = false) {
        if (!obj.symbolName || !obj.tableName || !obj.id) {
            return "param error"
        } else {
            let queryResult = this.selectObjectById(obj, isConnect, isOutSql);
            //不存在该记录,就插入
            if (!queryResult) {
                return this.insertObject(obj, isConnect, isOutSql);
            }
            if (this.checkSqlError(queryResult)) {
                //表示可以从库里查到
                if (queryResult.id) {
                    return this.updateObjectById(obj, isConnect, isOutSql);
                } else {
                    return this.insertObject(obj, isConnect, isOutSql);
                }
            } else {
                return queryResult;
            }
        }
    }

    updateObjectByKey (obj, key) {
        if (!obj.symbolName || !obj.tableName || !obj[key]) {
            return "param error"
        } else {
            let valueMapper = obj.dbMapper();
            if (!valueMapper) {
                return "param error";
            }
            let updateKeys = Reflect.ownKeys(valueMapper);
            //check obj 的对象key是否正确
            let objKeys = Reflect.ownKeys(obj);
            let checkResult = true;

            let setArr = [];
            //循环遍历传入对象的key的数组
            for (let i = 0;i < objKeys.length;i++) {
                if (!updateKeys.contains(objKeys[i])) {
                    checkResult = false;
                    break;
                }
                if (objKeys[i] !== key) {
                    if (typeof obj[objKeys[i]] != undefined && obj[objKeys[i]] !== -1 && obj[objKeys[i]] !== '') {
                        setArr.push(StringUtil.format(" {0} = '{1}' ", valueMapper[objKeys[i]], obj[objKeys[i]]));
                    }
                }
            }
            if (!checkResult) {
                return "param error";
            }

            let sql = StringUtil.format("update {0} set", obj.tableName);
            setArr.forEach((setValue, index) => {
                if (index !== (setArr.length - 1)) {
                    sql = sql + setValue + ",";
                } else {
                    sql = StringUtil.format("{0} {1} where {2} = '{3}'", sql, setValue, valueMapper(key), obj.id);
                }
            });
            let result = this.runSql(sql);
            return result === "sql error" ? result : 1
        }
    }

    /**
     * 统一的删除单个实体的方法
     * @param obj {1.param error, 2. sql error, 3.影响的行数}
     */
    deleteObjectById (obj) {
        if (!obj.symbolName || !obj.tableName || !obj.id) {
            return "param error"
        } else {
            let valueMapper = obj.dbMapper();
            if (!valueMapper) {
                return "param error";
            }
            let sql = StringUtil.format("delete from {0} where id = '{1}'", obj.tableName, obj.id);
            return this.runSql(sql);
        }
    }

    /**
     * 根据实体的某一属性的key值删除该行数据
     * @param obj
     */
    deleteObjectByKey (obj, key) {
        if (!obj.symbolName || !obj.tableName || !obj[key]) {
            return "param error"
        } else {
            let valueMapper = obj.dbMapper();
            if (!valueMapper) {
                return "param error";
            }
            let sql = StringUtil.format("delete from {0} where {1} = '{2}'", obj.tableName, valueMapper[key], obj[key]);
            return this.runSql(sql);
        }
    }

    /**
     * 根据id查询对象
     * @param obj
     * @returns {string}
     */
    selectObjectById (obj, isConnect = false, isOutSql = false) {
        if (!obj.symbolName || !obj.tableName || !obj.id) {
            return "param error"
        } else {
            let valueMapper = obj.dbMapper();
            if (!valueMapper) {
                return "param error";
            }
            let sql = StringUtil.format("select * from {0} where id = '{1}'", obj.tableName, obj.id);
            let result = isConnect ? this.runSqlWithoutConnect(sql) : this.runSql(sql);
            if (this.checkSqlError(result)) {
                if (result[0]) {
                    return obj.mappingModel(result[0]);
                } else {
                    return null;
                }
            } else {
                return result;
            }
        }
    }

    /**
     * 查询该表格所有的对象
     * @param obj
     * @returns {*}
     */
    selectAllObject (obj) {
        if (!obj.symbolName || !obj.tableName) {
            return "param error"
        } else {
            let valueMapper = obj.dbMapper();
            if (!valueMapper) {
                return "param error";
            }
            let sql = StringUtil.format("select * from {0}", obj.tableName);
            let result = this.runSql(sql);
            let objResult = [];
            result.forEach(temp => {
                objResult.push(obj.mappingModel(temp));
            });
            return objResult;
        }
    }

    /**
     * 根据筛选条件查询所有的实体
     * @param obj
     * @param keys
     * @return {*}
     */
    selectAllObjectByKeys (obj, keys) {
        if (!obj.symbolName || !obj.tableName || keys.length === 0) {
            return "param error"
        } else {
            let valueMapper = obj.dbMapper();
            if (!valueMapper) {
                return "param error";
            }
            let whereSql = "";
            keys.forEach((key, index) => {
                if (index !== keys.length - 1) {
                    whereSql += (valueMapper[key] + " = '" + obj[key] + "' and ")
                } else {
                    whereSql += (valueMapper[key] + " = '" + obj[key] + "'")
                }
            })
            let sql = StringUtil.format("select * from {0} where {1}", obj.tableName, whereSql);
            // console.log('sql',sql);
            let result = this.runSql(sql);
            let objResult = [];
            if (result) {
                result.forEach(temp => {
                    objResult.push(obj.mappingModel(temp));
                });
            }
            return objResult;
        }
    }

    checkSqlError (result) {
        if (result) {
            if (result === "sql error") {
                return false;
            }
            if (result === "param error") {
                return false;
            }
            return true;
        } else {
            return false;
        }
    }

    /**
     * 优化读写效率
     * @param chapters
     * @param lessons
     */
    updateChapterAndLesson (chapters, lessons) {
        let _this = this;
        this.sqlite.connect(this.dbFileName);
        for (let i = 0;i < chapters.length;i++) {
            _this.updateOrInsertObjectById(chapters[i], true);
        }

        for (let i = 0;i < lessons.length;i++) {
            _this.updateOrInsertObjectById(lessons[i], true);
        }
        this.sqlite.close();
    }

    /**
     * 优化读写效率
     * @param publishers
     * @param books
     */
    updatePublishAndBook (publishers, books) {
        let _this = this;
        this.sqlite.connect(this.dbFileName);
        for (let i = 0;i < publishers.length;i++) {
            _this.updateOrInsertObjectById(publishers[i], true);
        }
        for (let i = 0;i < books.length;i++) {
            _this.updateOrInsertObjectById(books[i], true);
        }
        this.sqlite.close();
    }

    /**
     * 批量更新下载课程
     * @param resultLesson
     * @param userId
     * @param fillTime
     */
    updateDownloadingLesson (resultLesson, userId, fillTime) {
        let _this = this;
        this.sqlite.connect(this.dbFileName);
        for (let i = 0;i < resultLesson.length;i++) {
            let downloader = new Downloader();
            downloader.id = resultLesson[i].SetDownloadId;
            downloader.isBook = "1";
            downloader.downloaderStatus = "0";
            downloader.userId = userId;
            downloader.fillTime = fillTime;
            _this.updateOrInsertObjectById(downloader, true);
        }
        this.sqlite.close();
    }
    //查询用户所有已下载的课程记录
    queryAllDownloadedLessonByUserId (userId) {
        const sql = StringUtil.format("SELECT * from t_download_complete where user_id = '{0}'", userId);
        const allLesson = this.runSql(sql)
        if (this.checkSqlError(allLesson)) {
            return allLesson
        } else {
            return allLesson
        }
    }
    // //删除用户所有已下载的课程
    // deleteAllDownloadedLessonByUserId (userId) {
    //     const sql = StringUtil.format("DELETE * from t_download_complete where user_id = '{0}'", userId);
    //     const allLesson = this.runSql(sql)
    //     if (this.checkSqlError(allLesson)) {
    //         return allLesson
    //     } else {
    //         return allLesson
    //     }
    // }

    addIsGlobal () {
        const sql = "SELECT * from sqlite_master where name = 't_user' and sql like '%is_global%'"
        const res = this.runSql(sql)
        if (!res || res.length === 0) {
            this.runSql("alter table t_user add column is_global TEXT")
        }
    }
}
